<!---
$Id$

Copyright 2007 Barney Boisvert (bboisvert@gmail.com).

Licensed under the Apache License, Version 2.0 (the "License"); you may
not use this file except in compliance with the License. You may obtain
a copy of the License at

    http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
--->
<cfcomponent output="false" extends="actionpacks.apputils.model.schema_tool.switchbasedmigration">
	
	<cffunction name="doMinorVersion" access="public" output="false" returntype="void">
		<cfargument name="minorVersion" type="numeric" required="true" />
		<cfset var rein = structNew()>
		<cfset var replaceCols = structNew()>
		<cfsetting requesttimeout="9999999">
		<cfset TabChar = Chr(9)>
		<cfset TabChar = "  ">
		<cfset NewLine = Chr(13)&chr(10)>

		<cfswitch expression="#minorVersion#">

 			<cfcase value="1">

				<cfquery name="updateEm" datasource="#variables.my.dsn#">
					SELECT * FROM people
				</cfquery>

 			</cfcase>
<!---
  			<cfcase value="2">
				<cfquery name="updateEm" datasource="#variables.my.dsn#">
					SELECT * from p_sessions
				</cfquery>
				<cfloop query="updateEm">
					<cfif isDate(enddate)>
						<cfset enddate = dateformat(enddate,"mm/dd/yyyy")>
						<cfquery name="updateEm" datasource="#variables.my.dsn#">
							UPDATE p_sessions SET enddate = #createOdbcDateTime(enddate)#
							WHERE p_sessions_id = #p_sessions_id#
						</cfquery>
					</cfif>
					<cfif isDate(startdate)>
						<cfset startdate = dateformat(startdate,"mm/dd/yyyy")>
						<cfquery name="updateEm" datasource="#variables.my.dsn#">
							UPDATE p_sessions SET startdate = #createOdbcDateTime(startdate)#
							WHERE p_sessions_id = #p_sessions_id#
						</cfquery>
					</cfif>
				</cfloop>
			</cfcase>
 			<cfcase value="3">
				<cfquery name="updateEm" datasource="#variables.my.dsn#">
					UPDATE p_sessions SET schoolyear = '|2005|'
					WHERE year(enddate) = 2005
				</cfquery>				
				<cfquery name="updateEm" datasource="#variables.my.dsn#">
					UPDATE p_sessions SET schoolyear = '|2006|'
					WHERE year(enddate) = 2006
				</cfquery>
				<cfquery name="updateEm" datasource="#variables.my.dsn#">
					UPDATE p_sessions SET schoolyear = '|2007|'
					WHERE year(enddate) = 2007
				</cfquery>				
			</cfcase>

 			<cfcase value="4">
				<cfquery name="updateEm" datasource="#variables.my.dsn#">
					ALTER TABLE p_teacher 
					DROP COLUMN firstname,
					DROP COLUMN lastname,
					DROP COLUMN middlename
				</cfquery>				
			</cfcase>

 			<cfcase value="5">
				<cfquery name="ar" datasource="#variables.my.dsn#">
				  DROP TABLE IF EXISTS p_teachereducation
  			</cfquery>
				<cfquery name="ar" datasource="#variables.my.dsn#">
				  DROP TABLE IF EXISTS p_educationlevels
  			</cfquery>
				<cfquery name="ar" datasource="#variables.my.dsn#">
					CREATE TABLE `p_educationlevels` (
					  `educationlevel_ID` int(11) NOT NULL auto_increment,
					  `educationLevelName` varchar(255) NOT NULL,
					  PRIMARY KEY  (`educationlevel_ID`)
					) ENGINE=InnoDB
  			</cfquery>
				<cfquery name="splitEm" datasource="#variables.my.dsn#">
					CREATE TABLE `p_teachereducation` (
					  `teacherEducation_ID` int(11) NOT NULL auto_increment,
					  `teacher_id` int(11) NOT NULL,
					  `educationlevel_id` int(11) NOT NULL,
					  PRIMARY KEY  (`teacherEducation_ID`),
					  KEY `pTekFk` (`teacher_id`),
					  KEY `edLevFk` (`educationlevel_id`),
					  CONSTRAINT `edLevFk` FOREIGN KEY (`educationlevel_id`) REFERENCES `p_educationlevels` (`educationlevel_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
					  CONSTRAINT `pTekFk` FOREIGN KEY (`teacher_id`) REFERENCES `p_teacher` (`p_Teacher_ID`) ON DELETE CASCADE ON UPDATE CASCADE
					) ENGINE=InnoDB
  			</cfquery>
				<cfquery name="splitEm" datasource="#variables.my.dsn#">
					SELECT DISTINCT levelofeducation,p_teacher_id FROM p_teacher
				</cfquery>
				<cfset les = "">
				<cfloop query="splitEm">

					<cfloop list="#levelofeducation#" delimiters="|" index="lev">
						<cfif NOT lev eq ",">
						  <cfif NOT listFindNoCase(les,lev)>
							  <cfset les = listAppend(les,lev)>
							  <cfquery name="addLe" datasource="#variables.my.dsn#">
								  INSERT INTO p_educationlevels (educationLevelName)
								  VALUES ('#replace(lev,"|","","all")#')
								</cfquery>
							</cfif>
						  <cfquery name="getLe" datasource="#variables.my.dsn#">
							  SELECT educationlevel_id FROM p_educationlevels
							  WHERE educationLevelName = '#replace(lev,"|","","all")#'
							</cfquery>
							
						  <cfquery name="chkLe" datasource="#variables.my.dsn#">
							  SELECT * FROM p_teachereducation 
							  WHERE teacher_id = #p_teacher_id#
							  AND educationlevel_id = #getLe.educationlevel_id#
							</cfquery>
							<cfif chkLe.recordcount eq 0>
							  <cfquery name="addLe" datasource="#variables.my.dsn#">
								  INSERT INTO p_teachereducation (teacher_id,educationlevel_id)
								  VALUES (#p_teacher_id#,#getLe.educationlevel_id#)
								</cfquery>
							</cfif>
						</cfif>
					</cfloop>
				
				</cfloop>
			</cfcase>

 			<cfcase value="6">
				<cfquery name="updateEm" datasource="#variables.my.dsn#">
					ALTER TABLE p_telephone 
					DROP COLUMN datesubmitted
				</cfquery>				
			</cfcase>
			
 			<cfcase value="7">
				<cfquery name="updateEm" datasource="#variables.my.dsn#">
					DROP TABLE IF EXISTS studentstatus_orphans
				</cfquery>
				<cfquery name="updateEm" datasource="#variables.my.dsn#">
					CREATE TABLE studentstatus_orphans (SELECT * FROM p_studentstatus WHERE student NOT IN
					(select p_student_id FROM p_student))
				</cfquery>
				<cfquery name="updateEm" datasource="#variables.my.dsn#">
					DELETE FROM p_studentstatus WHERE student NOT IN
					(select p_student_id FROM p_student)
				</cfquery>
				<cfquery name="updateEm" datasource="#variables.my.dsn#">
					ALTER TABLE p_studentstatus MODIFY student int (11)
				</cfquery>
				<cfquery name="updateEm" datasource="#variables.my.dsn#">
					ALTER TABLE p_studentstatus MODIFY `status` varchar (200)
				</cfquery>
				<cfquery name="updateEm" datasource="#variables.my.dsn#">
					ALTER TABLE p_student MODIFY session int (11)
				</cfquery>
				<cfquery name="updateEm" datasource="#variables.my.dsn#">
					ALTER TABLE p_studentstatus
					ADD FOREIGN KEY(`student`) REFERENCES p_student(p_student_id);
				</cfquery>				
				<cfquery name="updateEm" datasource="#variables.my.dsn#">
					ALTER TABLE p_student
					ADD FOREIGN KEY(`session`) REFERENCES p_sessions (p_sessions_id);
				</cfquery>
				<cfquery name="updateEm" datasource="#variables.my.dsn#">
					CREATE OR REPLACE VIEW vw_students_status_session AS select `p_student`.`p_Student_ID` AS `student_id`,`p_preksite`.`SiteName` AS `SiteName`,`p_student`.`LastName` AS `LastName`,`p_student`.`firstname` AS `firstname`,`p_student`.`MiddleName` AS `MiddleName`,`p_student`.`IDNumber` AS `idnumber`,`p_student`.`schoolyear` AS `schoolyear`,`p_studentstatus`.`status` AS `status`,`p_studentstatus`.`statusdate` AS `statusdate`,`p_sessions`.`ClassroomGroupName` AS `ClassroomGroupName`,`p_preksite`.`p_PreKSite_ID` AS `site_ID` from (((`p_preksite` join `p_student` on((`p_preksite`.`p_PreKSite_ID` = `p_student`.`site`))) join `p_studentstatus` on(((`p_student`.`p_Student_ID` = `p_studentstatus`.`student`) and (`p_studentstatus`.`p_StudentStatus_ID` = (select max(`p_studentstatus`.`p_StudentStatus_ID`) AS `MAX(p_studentstatus_id)` from `p_studentstatus` where (`p_studentstatus`.`student` = `p_student`.`p_Student_ID`)))))) join `p_sessions` on((`p_student`.`session` = `p_sessions`.`p_Sessions_ID`))) group by `p_preksite`.`p_PreKSite_ID`,`p_preksite`.`SiteName`,`p_student`.`p_Student_ID`,`p_student`.`firstname`,`p_student`.`LastName`,`p_student`.`MiddleName`,`p_studentstatus`.`status`,`p_studentstatus`.`statusdate`,`p_sessions`.`ClassroomGroupName` order by `p_student`.`LastName`
				</cfquery>
			</cfcase>
 --->

<!---
 			<cfcase value="1">
				<cfquery name="updateEm" datasource="#variables.my.dsn#">
					UPDATE p_sessions SET schoolyear = '|2006|'
					WHERE schoolyear IS NULL OR schoolyear = ''
				</cfquery>				
			</cfcase>
			<cfcase value="2">
				<cfscript>
					rein["sourceTable"]["tablename"] = "p_sessions";
					rein["sourceTable"]["where"] = "schoolyear = '|2006|'";
					rein["sourceTable"]["prikey"] = "p_sessions_id";
					replaceCols["p_sessions_id"] = 0;
					replaceCols["schoolyear"] = '|2007|';
					replaceCols["classroomGroupName"] = 'append: (2007)';
					rein["sourceTable"]["replaceCols"] = replaceCols;
					rein["subTables"]["p_ecersrscore"]["prikey"] = "p_ecersrscore_id";
					rein["subTables"]["p_ecersrscore"]["sourcekey"] = "classroom";
					replaceCols["p_ecersrscore_id"] = 0;
					rein["subTables"]["p_ecersrscore"]["replaceCols"] = replaceCols;
					reinsertTable(rein);
				</cfscript>
			</cfcase>

			<cfcase value="3">
				<cfscript>
					rein["sourceTable"]["tablename"] = "p_sessions";
					rein["sourceTable"]["where"] = "schoolyear = '|2006|'";
					rein["sourceTable"]["prikey"] = "p_sessions_id";
					replaceCols["p_sessions_id"] = 0;
					replaceCols["schoolyear"] = '|2008|';
					replaceCols["classroomGroupName"] = 'append: (2008)';
					rein["sourceTable"]["replaceCols"] = replaceCols;
					rein["subTables"]["p_ecersrscore"]["prikey"] = "p_ecersrscore_id";
					rein["subTables"]["p_ecersrscore"]["sourcekey"] = "classroom";
					replaceCols["p_ecersrscore_id"] = 0;
					rein["subTables"]["p_ecersrscore"]["replaceCols"] = replaceCols;
					reinsertTable(rein);				
				</cfscript>
			</cfcase>
 --->

<!---
			<cfcase value="">
				<cfquery datasource="#variables.my.dsn#" name="">
				</cfquery>
			</cfcase>

--->
		</cfswitch>
	</cffunction>
	
<cfoutput></cfoutput>
</cfcomponent>
